<?php

class ReportController extends Controller
{
	public function actionIndex()
	{
		$sql = "SELECT member_firstname,member_lastname FROM ts_member"; // ����� SQL ����ͧ���

          $dataProvider = new CSqlDataProvider($sql, array(    // ��� sql �ŧ�� dataProvider
          'pagination' => array(
          'pageSize' => 10,        // ��˹����˹�ҹ֧�ʴ� 10 ��¡��
          ),
          ));

          $this->render('index', array('dataProvider' => $dataProvider));   // �觵������ѧ view
		//$this->render('index');
	}
        
        public function actionReportOrder()
	{
		$sql = "SELECT o.id,o.order_date,member.member_firstname,member.member_lastname,pt.payment_type_name_thai ";
                $sql .= ",o.order_amount,o.member_id,o.order_tracking_status FROM ts_order o ";
                $sql .= "inner join ts_payment p on o.id = p.order_id ";
                $sql .= "inner join mt_payment_type pt on p.payment_type_id = pt.id ";
                $sql .= "inner join ts_member member on o.member_id = member.id ";

          $dataProvider = new CSqlDataProvider($sql, array(    
          'pagination' => array(
          'pageSize' => 10,        
          ),
          ));

          $this->render('ReportOrder', array('dataProvider' => $dataProvider));  
	}
        
        public function actionReportOrderDetail($id)
	{
//		$sql = "SELECT o.id,o.order_date,member.member_firstname,member.member_lastname,pt.payment_type_name_thai ";
//                $sql .= ",o.order_amount,o.member_id,p.product_name_eng,odd.product_cost,odd.order_detail_date FROM ts_Order o ";
//                $sql .= "inner join mt_payment_type pt on o.payment_type_id = pt.id ";
//                $sql .= "inner join ts_member member on o.member_id = member.id ";
//                $sql .= "inner join ts_order_detail odd on o.id = odd.order_id ";
//                $sql .= "inner join mt_product p on odd.product_id = p.id ";
//                $sql .= "where o.member_id = $id";
                $sql = "SELECT o.id,o.order_date,member.member_firstname,member.member_lastname,pt.payment_type_name_thai ";
                $sql .= ",o.order_amount,o.member_id,p.product_name_eng,odd.product_cost,odd.order_detail_date ";
                $sql .= ",odd.order_detail_quantity,(odd.order_detail_quantity*odd.product_cost)as total ";
                $sql .= "FROM ts_order o ";
                $sql .= "inner join ts_payment pay on o.id = pay.order_id ";
                $sql .= "inner join mt_payment_type pt on pay.payment_type_id = pt.id ";
                $sql .= "inner join ts_member member on o.member_id = member.id ";
                $sql .= "inner join ts_order_detail odd on o.id = odd.order_id ";
                $sql .= "inner join mt_product p on odd.product_id = p.id ";
                $sql .= "where o.id = $id";

          $dataProvider = new CSqlDataProvider($sql, array(    
          'pagination' => array(
          'pageSize' => 10,        
          ),
          ));

          $this->render('ReportOrderDetail', array('dataProvider' => $dataProvider));  
	}
        
        public function actionReportMember()
	{
            $sql = "SELECT m.id,m.member_firstname,m.member_lastname,m.member_email ";
            $sql .= ",m.member_tel,m.member_level,g.member_group_name_thai,g.member_group_name_eng FROM ts_member m ";
            $sql .= "inner join mt_member_group g on m.member_group_id = g.id ";
            $sql .= "where m.member_level not in('admin');";
            $dataProvider = new CSqlDataProvider($sql, array(    
            'pagination' => array(
            'pageSize' => 10,       
          ),
          ));

          $this->render('ReportMember', array('dataProvider' => $dataProvider));   // �觵������ѧ vie
	}
        
        public function actionReportTopSale()
	{
		$sql = "SELECT od.id,p.product_name_eng,SUM(od.order_detail_quantity)as totalQty,SUM(od.product_cost*od.order_detail_quantity)as costtotal FROM ts_order_detail od ";
                $sql .= "inner join mt_product p on od.product_id = p.id ";
                $sql .= "group by p.product_name_eng ";
                $sql .= "order by costtotal desc ";
                $sql .= "limit 10;";

          $dataProvider = new CSqlDataProvider($sql, array(    
          'pagination' => array(
          'pageSize' => 10,      
          ),
          ));

          $this->render('ReportTopSale', array('dataProvider' => $dataProvider));   
	}
        
        public function actionReportTopSaleMin()
	{
		$sql = "SELECT od.id,p.product_name_eng,SUM(od.order_detail_quantity)as totalQty,SUM(od.product_cost*od.order_detail_quantity)as costtotal FROM ts_order_detail od ";
                $sql .= "inner join mt_product p on od.product_id = p.id ";
                $sql .= "group by p.product_name_eng ";
                $sql .= "order by costtotal asc ";
                $sql .= "limit 10;";

          $dataProvider = new CSqlDataProvider($sql, array(    
          'pagination' => array(
          'pageSize' => 10,      
          ),
          ));

          $this->render('ReportTopSaleMin', array('dataProvider' => $dataProvider));   
	}
        
        public function actionReportProductBalance()
	{
		$sql = "SELECT st.id,p.product_name_eng,IFNULL(st.stock_product_quantity,0)as stock_product_quantity ";
                $sql .= "FROM ts_stock st ";
                $sql .= "left outer join mt_product p on st.product_id = p.id ";
                $sql .= "order by product_id  ";


          $dataProvider = new CSqlDataProvider($sql, array(    
          'pagination' => array(
          'pageSize' => 10,      
          ),
          ));

          $this->render('ReportProductBalance', array('dataProvider' => $dataProvider));   
	}

	// Uncomment the following methods and override them if needed
	/*
	public function filters()
	{
		// return the filter configuration for this controller, e.g.:
		return array(
			'inlineFilterName',
			array(
				'class'=>'path.to.FilterClass',
				'propertyName'=>'propertyValue',
			),
		);
	}

	public function actions()
	{
		// return external action classes, e.g.:
		return array(
			'action1'=>'path.to.ActionClass',
			'action2'=>array(
				'class'=>'path.to.AnotherActionClass',
				'propertyName'=>'propertyValue',
			),
		);
	}
	*/
}